package com.fsds.crem.core.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.transformer.XLSTransformer;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

public class ExcelUtil {

	 /**
     * 把选中列转换成EXCEL，并下载
     * @param beans                  转换使用的内容
     * @param templateExcel          模版文件名， 必须存放在预定义的文件夹下
     * @param downloadFileName       下载显示的文件名        
     * @param excludeRowName         需要打印栏位名称集合
     * @param hasSearchInfo          是否有查询信息行
     * @param lastCellNum            最后一列列号
     * @param lastRowNum             最后一行行号
     * @throws Exception             处理中遇到的异常
     */
    public void transformSelectAndDownloadExcelDT(Map<String, Object> beans,
    		String templateExcel,
    		String downloadFileName,String excludeRowName, boolean hasSearchInfo, Map<String, Integer> cellMap, int lastCellNum, int lastRowNum) throws Exception
    {
    	// 获取源模板
		HSSFWorkbook wb = findWorkBookByFileName(templateExcel);
		
		// 以下处理模板
		String[] showRows = excludeRowName.split(",");
		
		createModelDT(wb, showRows, hasSearchInfo, cellMap, lastCellNum, lastRowNum);
		
		// 生成临时文件并获取临时文件名
		String tempFileName = getTempFileName(wb);
		
		// 生成Excel并汇出
		transformAndDownloadExcel(beans,tempFileName,downloadFileName);
		
		// 删除临时文件
//		deleteTempFile(tempFileName);
    }

    /**
  	 * 根据模板名称得到工作页
  	 * @return
  	 */
    public HSSFWorkbook findWorkBookByFileName(String fileName) throws Exception {
    	
    	String templatePath = "f:/" + fileName;
		File file = new File(templatePath);
		FileInputStream inputStream = new FileInputStream(file);
    	HSSFWorkbook wb = new HSSFWorkbook(inputStream);
    	inputStream.close();
    	return wb;
    }

	/**
	 * 建立新的模板-双行表头
	 * @param wb 工作表
	 * @param showRows 显示的列ID数组
	 * @param hasSearchInfo 是否有查询条件行
	 * @param lastCellNum 最后列列号
	 * @param lastRowNum 最后行行号
	 */
	public void createModelDT(HSSFWorkbook wb, String[] showRows, boolean hasSearchInfo, Map<String, Integer> cellMap, int lastCellNum, int lastRowNum) {
		
		int dataRowNum;
		int endCellNum = showRows.length;// 页面最终列数
		
		if(hasSearchInfo){
			
			dataRowNum = 9;
		}else{
			
			dataRowNum = 8;
		}
		
		endCellNum = createData(wb, showRows, dataRowNum, endCellNum, true, lastCellNum);
		
		
		// =================处理标题、印表信息、查询条件、公司名称显示===================
		HSSFSheet sheet = wb.getSheetAt(0);
		
		// 标题所在行号
		int titleRowNum = 1;
		createTitle(endCellNum, sheet, titleRowNum);
		createBankInfo(endCellNum, sheet, lastRowNum);
		if (hasSearchInfo) {
			createSearchInfo(dataRowNum-4, endCellNum, sheet);
		}
		int infoBegin = 2; // 列表信息起始行号 = 2;
		createPrintInfo(endCellNum, sheet, infoBegin);
	
		// 双行表头合并
		HSSFRow dataRow = wb.getSheetAt(0).getRow(dataRowNum-1);
		for(int i=0; i<endCellNum; i++){
			if(!ApplicationUtils.isEmpty(dataRow.getCell(i))){
				
				String thisText = dataRow.getCell(i).getRichStringCellValue().getString().trim();// 单元格中文本
				// 若存在此Key，需合并单元格
				if(cellMap.containsKey(thisText)){
					int fixCell = cellMap.get(thisText); //合并列数
					// 如果合并列数为1，则上下合并单元格
					if(fixCell == 1){
						sheet.addMergedRegion(new CellRangeAddress(dataRowNum-3, (short)i, dataRowNum-2, (short)i));
//						sheet.addMergedRegion(new Region(dataRowNum-3, (short)i, dataRowNum-2, (short)i));
					}
					// 如果合并列数不为1（大于一），则合并fixCell列
					else{
						sheet.addMergedRegion(new CellRangeAddress(dataRowNum-3, (short)i, dataRowNum-3, (short)(i+fixCell-1)));
//						sheet.addMergedRegion(new Region(dataRowNum-3, (short)i, dataRowNum-3, (short)(i+fixCell-1)));
					}
				}
			}
		}
	}

    /**
     * 处理时间、印表人显示
     * @param endCellNum
     * @param sheet
     */
    @SuppressWarnings("deprecation")
	public void createPrintInfo(int endCellNum, HSSFSheet sheet, int infoBegin) {
		
		// 印表人、时间右移    若小于等于两列，则无需处理
		if(endCellNum>2){
			
			for(int beginIndex = infoBegin; beginIndex < infoBegin + 3; beginIndex++){
				
				HSSFRow timeRow = sheet.getRow(beginIndex);
				timeRow.createCell((short) (endCellNum-1));
				timeRow.getCell(endCellNum-1).setCellValue(timeRow.getCell(1).getStringCellValue());
				timeRow.getCell(endCellNum-1).setCellType(timeRow.getCell(1).getCellType());
				timeRow.getCell(endCellNum-1).setCellStyle(timeRow.getCell(1).getCellStyle());
				timeRow.removeCell(timeRow.getCell(1));
				
				timeRow.createCell((short) (endCellNum-2));
				timeRow.getCell(endCellNum-2).setCellValue(timeRow.getCell(0).getStringCellValue());
				timeRow.getCell(endCellNum-2).setCellType(timeRow.getCell(0).getCellType());
				timeRow.getCell(endCellNum-2).setCellStyle(timeRow.getCell(0).getCellStyle());
				timeRow.removeCell(timeRow.getCell(0));
			}
		}
	}
    
    /**
     * 查询条件
     * @param searchNum 查询行号
     * @param endCellNum 最后列列号
     * @param sheet 工作表
     */
    public void createSearchInfo(int searchNum, int endCellNum, HSSFSheet sheet) {
    	
		// 查询条件
    	sheet.addMergedRegion(new CellRangeAddress(searchNum, (short)0, searchNum, (short)(endCellNum-1)));
//		sheet.addMergedRegion(new Region(searchNum, (short)0, searchNum, (short)(endCellNum-1)));
	}

	/**
	 * 机构名称
	 * @param dataRowNum
	 * @param endCellNum
	 * @param sheet
	 */
	public void createBankInfo(int endCellNum, HSSFSheet sheet, int rowNum) {
		// 合并机构名称
		sheet.addMergedRegion(new CellRangeAddress(rowNum-1, (short) 0, rowNum-1,
				(short) (endCellNum - 1)));
//		sheet.addMergedRegion(new Region(rowNum-1, (short) 0, rowNum-1,
//				(short) (endCellNum - 1)));
	}

    /**
     * 数据
     * @param wb
     * @param showRows
     * @param dataRowNum
     * @param endCellNum
     * @return
     */
    @SuppressWarnings("deprecation")
	public int createData(HSSFWorkbook wb, String[] showRows, int dataRowNum, int endCellNum, boolean doubleTitle, int lastCellNum) {
		
		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFRow thisRow = sheet.getRow(dataRowNum-1);
		String sumWord = null;
		HSSFRow getSumRow = sheet.getRow(dataRowNum);
		if (!ApplicationUtils.isEmpty(getSumRow)) {
			HSSFCell getSumCell = sheet.getRow(dataRowNum).getCell(0);
			sumWord = ApplicationUtils.isEmpty(getSumCell)?null:getSumCell.getStringCellValue();// 获取合计栏位字段
		}
		
		// ============================删除列=================================
		for(int i=lastCellNum-1; i>=0; i--){
			// 如果当前列不为空
			if(!ApplicationUtils.isEmpty(thisRow.getCell(i))){
				String thisText = thisRow.getCell(i).getRichStringCellValue().getString().trim();// 单元格中文本
				if(!ApplicationUtils.isEmpty(thisText)){
					
					boolean needDelete = true;
					for (String thisRowId : showRows) {
						if (thisText.indexOf(thisRowId)>=0) {
							needDelete = false;
							break;
						}
					}
					
					// 双行表头时则删除四行
					int dataRows = 2;
					if(doubleTitle){
						dataRows = 3;
					}
					
					// 如果当前单元格对应栏位不在数组中
					if(needDelete){
					
						//循环标题行、数据行、合计行，有值删除，单元格不存在时新建再处理
						for(int j=dataRowNum-dataRows; j<=dataRowNum; j++){
							if(!ApplicationUtils.isEmpty(sheet.getRow(j))){
								HSSFRow updateRow = sheet.getRow(j);
								updateRow.removeCell(updateRow.getCell(i));
								updateRow.createCell((short) i);
							
								// 当有单元格删除后，此行右侧单元格左移
								for(int nowCell=i; nowCell<lastCellNum; nowCell++){
									if(nowCell+1 != lastCellNum){
										updateRow.getCell(nowCell).setCellValue(updateRow.getCell(nowCell+1).getStringCellValue());
										updateRow.getCell(nowCell).setCellType(updateRow.getCell(nowCell+1).getCellType());
										updateRow.getCell(nowCell).setCellStyle(updateRow.getCell(nowCell+1).getCellStyle());
										
										//标题行时处理宽度
										if(j==dataRowNum-dataRows){
											sheet.setColumnWidth((short)nowCell, sheet.getColumnWidth((short)(nowCell+1)));
										}
									}else{
										updateRow.createCell((short) nowCell);
										
										//标题行时处理宽度
										if(j==dataRowNum-dataRows){
											sheet.setColumnWidth((short)(nowCell), (short)2304);
										}
									}
								}
							}
						}
					}
				}
			}
		}

		endCellNum = createSumInfo(wb, dataRowNum, endCellNum, sheet, lastCellNum, sumWord, doubleTitle);
		avgColumnWidth(endCellNum, sheet);
		return endCellNum;
	}

	/**
	 * 处理合计
	 * @param wb
	 * @param dataRowNum
	 * @param endCellNum
	 * @param sheet
	 * @param lastCellNum
	 * @return
	 */
	@SuppressWarnings("deprecation")
	private int createSumInfo(HSSFWorkbook wb, int dataRowNum, int endCellNum, HSSFSheet sheet, int lastCellNum, String sumWord, boolean doubleTitle) {
		// ============================处理合计============================
		HSSFRow sumRow = sheet.getRow(dataRowNum);
		
		// 如果存在合计行，进行合计计算
		if(!ApplicationUtils.isEmpty(sumRow)){
			int sumFirst = -1;
			for(int sumCell=0; sumCell<lastCellNum; sumCell++){
				if(!ApplicationUtils.isEmpty(sumRow.getCell(sumCell))){
					String thisText = sumRow.getCell(sumCell).getRichStringCellValue().getString().trim();// 单元格中文本
					if(thisText.indexOf("$")==0){
						sumFirst = sumCell;
						break;
					}
				}
			}
			
			// 双行表头时则处理四行
			int dataRows = 2;
			if(doubleTitle){
				dataRows = 3;
			}
			
			// 合计栏位在第一列情况,所有资料栏位右移一个位，合计Row首单元格添加合计字段
			if(sumFirst == 0){
				for(int i=lastCellNum; i>=0; i--){
					for(int j=dataRowNum-dataRows; j<=dataRowNum; j++){
						if(!ApplicationUtils.isEmpty(sheet.getRow(j))){
							HSSFRow updateRow = sheet.getRow(j);
							updateRow.createCell((short) i);
							
							if(i != 0){
								updateRow.getCell(i).setCellValue(updateRow.getCell(i-1).getStringCellValue());
								updateRow.getCell(i).setCellType(updateRow.getCell(i-1).getCellType());
								updateRow.getCell(i).setCellStyle(updateRow.getCell(i-1).getCellStyle());
								
							}else{
								updateRow.createCell((short) i);
							}
						}
					}
					
					// 处理宽度
					sheet.setColumnWidth((short)i, sheet.getColumnWidth((short)(i-1)));
				}
				
				HSSFCell sumFirstCell = sumRow.createCell((short)0);
				sumFirstCell.setCellValue(sumWord);
				sheet.setColumnWidth((short)0, (short)2749);
				
				// 合计字段加加粗居中
			    HSSFCellStyle style = wb.createCellStyle();
			 	HSSFFont font = wb.createFont();
			 	font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
			 	font.setFontName("宋体");
		        font.setFontHeightInPoints((short)12);
			 	style.setFont(font);
			 	style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				sumFirstCell.setCellStyle(style);
				
				// 因添加合计栏，最终列数+1
				endCellNum++;
			}
			
			// 合计栏位不在第一列
			else if(sumFirst > 0){
				HSSFCell sumFirstCell = sumRow.createCell((short)0);
				sumFirstCell.setCellValue(sumWord);
				
				// 合计字段加加粗居中
			    HSSFCellStyle style = wb.createCellStyle();
			    style.setBorderBottom(HSSFCellStyle.BORDER_THIN  );//下边框
				style.setBorderLeft(HSSFCellStyle.BORDER_THIN  );//左边框
				style.setBorderRight(HSSFCellStyle.BORDER_THIN  );//右边框
				style.setBorderTop(HSSFCellStyle.BORDER_THIN  );//上边框
				
				//字体处理
			 	HSSFFont font = wb.createFont();
			 	font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
			 	font.setFontName("宋体");
		        font.setFontHeightInPoints((short)12);
			 	style.setFont(font);
			 	style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				sumFirstCell.setCellStyle(style);
				sheet.addMergedRegion(new CellRangeAddress(dataRowNum, (short)0, dataRowNum, (short)(sumFirst-1)));
//				sheet.addMergedRegion(new Region(dataRowNum, (short)0, dataRowNum, (short)(sumFirst-1)));
			}
			
			//不存在合计栏位
			else{
				if(!ApplicationUtils.isEmpty(sumRow.getCell(0))){
					String thisText = sumRow.getCell(0).getRichStringCellValue().getString().trim();// 单元格中文本
					// 有合计字段,删除合计行
					if(!ApplicationUtils.isEmpty(thisText) && thisText.indexOf(sumWord)>=0){
						sheet.removeRow(sumRow);
					}
				}
			}
		}
		return endCellNum;
	}

	/**
	 * 最终少于等于4列，则平均宽度
	 * @param endCellNum
	 * @param sheet
	 */
	private void avgColumnWidth(int endCellNum, HSSFSheet sheet) {
		
		// 最终少于等于4列，则平均宽度
		if(endCellNum<=4){
			
			for(int i = 0; i < endCellNum; i++){
				
				sheet.setColumnWidth((short) i, (short)(32760/endCellNum));
			}
		}
	}
	
    /**
     * 标题
     * @param endCellNum
     * @param sheet
     */
    public void createTitle(int endCellNum, HSSFSheet sheet, int titleRowNum) {
		// 合并标题
    	sheet.addMergedRegion(new CellRangeAddress(titleRowNum, (short)0, titleRowNum, (short)(endCellNum-1)));
//		sheet.addMergedRegion(new Region(titleRowNum, (short)0, titleRowNum, (short)(endCellNum-1)));
		
	}
    
    /**
  	 * 生成临时文件
  	 * @return 返回临时文件名
  	 */
    public String getTempFileName(HSSFWorkbook wb) throws Exception {
    	long current = System.currentTimeMillis();
	    String newFileName = "f:/" + current + ".xls";
	    FileOutputStream outputStream = new FileOutputStream(new File(newFileName));
	    wb.write(outputStream);
	    outputStream.close();
    	return current + ".xls";
    }
    
    /**
  	 * 删除临时文件
  	 * @return
  	 */
//    public void deleteTempFile(String fileName) throws Exception {
//    	
//	    String newFileName = WebappHelper.getFileRealPath("f:/" + fileName);
//	    File file = new File(newFileName);
//	    file.delete();
//    }

    /**
     * 转换 EXCEL并下载
     * @param beans                  转换使用的内容
     * @param templateExcel          模版文件名， 必须存放在预定义的文件夹下
     * @param downloadFileName       下载显示的文件名
     * @throws Exception             处理中遇到的异常
     */
    public String transformAndDownloadExcel(Map<String, Object> beans,
    		String templateExcel,
    		String downloadFileName,String ...excludeSheetName) throws Exception
    {
    	String fileName = transformAndDownloadExcel(beans,templateExcel,downloadFileName,null,excludeSheetName);

        return fileName;
    }

	/**
     * 转换 EXCEL并下载
     * @param beans                  转换使用的内容
     * @param templateExcel          模版文件名， 必须存放在预定义的文件夹下
     * @param downloadFileName       下载显示的文件名
     * @throws Exception             处理中遇到的异常
     */
    public String transformAndDownloadExcel(Map<String, Object> beans,
                                             String templateExcel,
                                             String downloadFileName,File imageFile,String ...excludeSheetName) throws Exception
    {
        String fileName = transformTempExcel(beans, templateExcel,downloadFileName, excludeSheetName);    
        formulaSetting(beans, fileName,excludeSheetName);
        
        return fileName;
    }

    /**
     * 根据EXCEL模版转换产生EXCEL
     * @param beans             转换使用的内容
     * @param templateExcel     模版文件名， 必须存放在预定义的文件夹下
     * @return                  转换产生的临时文件名
     * @throws java.io.IOException      处理中遇到的异常
     * @throws InvalidFormatException 
     * @throws ParsePropertyException 
     */
    
	@SuppressWarnings("unchecked")
	protected String transformTempExcel(Map beans, String templateExcel, String downloadFileName, String ... excludeSheetName) throws Exception
    {
        long current = System.currentTimeMillis();
        String date = new SimpleDateFormat("yyyyMMdd").format(new Date());
        String sysPath = ResourceUtils.getSysPath();
        File folder = new File(sysPath.substring(0, sysPath.length() - 1));
        String exportPath = folder.getParentFile().getParent() + "/exportExcel";
        String fileName = exportPath + "/" + date + "/" + downloadFileName + current + ".xls";
        File file=new File(exportPath + "/" + date + "/");
        if (!file.exists()) {
        	file.mkdirs();
        }
        XLSTransformer transformer = new XLSTransformer();
//        String templatePath = ResourceUtils.getConfigByName("EXCEL_TEMPLATE_URL") + "/" + templateExcel;
        String templatePath = sysPath + "template/" + templateExcel;
        if(excludeSheetName.length > 0)
        	transformer.setSpreadsheetsToRemove(excludeSheetName);
        transformer.transformXLS(templatePath, beans, fileName);        
        return fileName;
    }

    /**
     * 下载文件,在下载完成后，将自动删除文件
     * @param sourceFile            下载的源文件名， 文件全路径包含
     * @param downloadFileName      下载显示的文件名
     * @throws Exception            处理中发生的异常
     */
    protected void formulaSetting(Map<String, Object> beans, String sourceFile,String ...excludeSheetName) throws Exception {
        String contentType = "application/octet-stream;charset=UTF-8";
		
		File file = new File(sourceFile);
		
	        FileInputStream inputStream = new FileInputStream(file);
	        
	    	HSSFWorkbook workBook = new HSSFWorkbook(inputStream);
	    	
	    	if(excludeSheetName.length > 0){
	    		
	    		List<String> excludeSheetList = Arrays.asList(excludeSheetName);
	    		
	    		int sheetNum = workBook.getNumberOfSheets();
	    		
	    		for(int i = 0; i < sheetNum; i++){
	    			
	    			String sheetName = workBook.getSheetName(i);
	    			
	    			LogUtil.info("sheetName:"+sheetName);
	    			
	    			// 有数据的sheet
	    			if(excludeSheetList.contains(sheetName.toLowerCase())){
    					
	    				LogUtil.info("有数据");
	    				
    					HSSFSheet sheet = workBook.getSheet(sheetName);
    	    			Map<String, Object> map = (Map<String, Object>) 
    	    					beans.get("day"+sheetName.substring(sheetName.length()-2));
    	    			workBook.setSheetName(i, map.get("SaleDailyDate").toString().substring(5));
    	    			
    	    			sheet.setForceFormulaRecalculation(true);
    				}
	    			else{
	    				
	    				LogUtil.info("无数据");
	    				
	    				workBook.removeSheetAt(workBook.getSheetIndex(sheetName));
	    			}
	    		}
	    	}
	    	else{
	    		
	    		HSSFSheet sheet = workBook.getSheetAt(0);
	    		sheet.setForceFormulaRecalculation(true);
	    		workBook.setSheetName(0, beans.get("SaleDailyDate").toString().substring(5));
	    		
				//设置两个数相加除以2的公式
//		        sheet.getRow(4).getCell(3).setCellFormula("(C6+C7+C8+C9+C10+C11+C12)");
	    	}

	        FileOutputStream fileOut = new FileOutputStream(sourceFile); 
	        //写入excel文件
	        workBook.write(fileOut); 
	        fileOut.close(); 
		
    }
    
    public static void main(String[] args) {

	     Calendar calendar = Calendar.getInstance();  
	     calendar.setTime(new Date());  
	     //第几周  
	     int week = calendar.get(Calendar.WEEK_OF_MONTH);  
	     //第几天，从周日开始  
	     int day = calendar.get(Calendar.DAY_OF_MONTH);
	     System.err.println(day);
	}
}
